package com.tlb.ejb;

import java.sql.*;
import javax.sql.*;

import com.tlb.dto.ThreadList;
import com.tlb.dto.ThreadDataView;
import com.tlb.dto.PostView;
import com.tlb.dto.PostFlag;
import com.tlb.dto.PostContent;
import com.tlb.web.*;

import java.math.BigInteger;
import java.util.*;
import javax.persistence.*;

public class ThreadDAO {

    private static com.tlb.web.Util util = com.tlb.web.Util.getUtil();
    public ThreadDAO() {
    }
    
    
    public void updatePost(PostContent p) {
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            p.setContent(fixHTML(p.getContent()));
            em.merge(p);
            
            tx.commit();
            em.close();
            
            
        } catch(Exception e) {
            System.err.println("Error updating post: " + e.getMessage());
            e.printStackTrace();
        }
        
    }

    public PostContent getPostById(int postId) {
        PostContent p = null;
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            p = (PostContent)em.find(PostContent.class, new Integer(postId));
            
            tx.commit();
            em.close();
            
            
        } catch(Exception e) {
            System.err.println("Error in get post by id: " + e.getMessage());
            e.printStackTrace();
        }
        return p;
    }

    public void savePostFlag(PostFlag pf) {
        
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            em.persist(pf);
            
            tx.commit();
            em.close();
                        
        } catch(Exception e) {
            System.err.println("Error saving post flag: " + e.getMessage());
            e.printStackTrace();
        }
    }    
    
    public String fixHTML(String str) {
        if(str == null)
            str = "";
        str = str.replaceAll("<i>", "!!!i!!!");
        str = str.replaceAll("</i>", "!!!/i!!!");
        str = str.replaceAll("<tt>", "!!!tt!!!");
        str = str.replaceAll("</tt>", "!!!/tt!!!");
        str = str.replaceAll("<", "&lt;");
        str = str.replaceAll(">", "&gt;");
        str = str.replaceAll("!!!i!!!", "<i>");
        str = str.replaceAll("!!!/i!!!", "</i>");
        str = str.replaceAll("!!!tt!!!", "<tt>");
        str = str.replaceAll("!!!/tt!!!", "</tt>");
        return str;
    }        
    
    
    public void addReply(PostView pv) {
        
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            pv.setContent(fixHTML(pv.getContent()));
            
            em.createNativeQuery(
                "call addpost(:threadId, :treeId, :parentId, :monikerId, :content, :nsfw);"
            )
            .setParameter("threadId", pv.getThreadId())
            .setParameter("treeId", pv.getTreeId())
            .setParameter("parentId", pv.getPostId())
            .setParameter("monikerId", pv.getMonikerId())
            .setParameter("content", pv.getContent())
            .setParameter("nsfw", pv.getNsfw())
            .executeUpdate();
            
            tx.commit();
            em.close();
            
            
        } catch(Exception e) {
            System.err.println("Error adding a reply: " + e.getMessage());
            e.printStackTrace();
        }
        
    }

    public int addThread(PostView pv) {
    	Connection con = null;
    	PreparedStatement pstmt = null;
    	ResultSet rs = null;
        try {
            con = Util.getUtil().getRawConnection();
            pstmt = con.prepareStatement(
                "call addnewpost(?, ?, ?, ?);"
            );
            
            pstmt.setInt(1, pv.getMonikerId());
            pstmt.setString(2, pv.getTitle());
            pstmt.setString(3, pv.getContent());
            pstmt.setBoolean(4, pv.getNsfw());
            
            rs = pstmt.executeQuery();
            if(rs.next())
            	return rs.getInt(1);
                     
        } catch(Exception ee) {
        	System.out.println("Exception adding thread: " + ee.getMessage());
        	ee.printStackTrace();
        } finally {
        	try {
        		if(rs != null)
        			rs.close();
        		if(pstmt != null)
        			pstmt.close();
        		if(con != null)
        			con.close();
        	} catch(Exception e) {}
        }
        
        
        return -1;
    }
    /*
    //Had trouble getting this to work with different versions of JPA.  Sometimes getSingleResultSet returns a byte[], sometimes a Number.
    //Could probably change to use an entity class and do a createQuery...  Going with above plain JDBC instead.
    public int addThread(PostView pv) {
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            pv.setContent(fixHTML(pv.getContent()));
            
            Object o = em.createNativeQuery(
                "call addnewpost(:monikerId, :title, :content, :nsfw);", Integer.class
            )
            .setParameter("monikerId", pv.getMonikerId())
            .setParameter("title", pv.getTitle())
            .setParameter("content", pv.getContent())
            .setParameter("nsfw", pv.getNsfw())
            .getSingleResult();
            
            tx.commit();
            em.close();
                     
            if(o != null)
                return ((Number)o).intValue();
            
        } catch(Exception e) {
            System.err.println("Error adding a thread: " + e.getMessage());
            e.printStackTrace();
        }
        return -1;
    }
    */
    public List<PostView> getPosts(int posterId, int threadId) {
        List<PostView> rs = null;
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            rs = em.createNativeQuery(
                "SELECT \n" +
                "  node.nsfw, node.spam, node.tos, \n" +
                "  node.postId as postId, parent.threadId, node.treeId, (COUNT(parent.postId) - 1) AS depth, \n" +
                "  moniker.monikerId, moniker.name as monikerName, moniker.tagLine, moniker.postCount, moniker.showCounter, \n" +
                "  content.title, content.content, node.postDate as postDate, node.lft, node.rgt, moniker.posterId \n" +
                "FROM \n" +
                "  post AS node, \n" +
                "  post AS parent, \n" +
                "  postcontent As content, \n" +
                "  moniker AS moniker \n" +
                "  left outer join poster viewer on 1=1 \n" +
                "WHERE \n" +
                "  node.lft BETWEEN parent.lft AND parent.rgt \n" +
                "  and node.postId = content.postId \n" +
                "  and node.threadId = parent.threadId \n" +
                "  and node.treeId = parent.treeId \n" +
                "  and content.monikerId = moniker.monikerId \n" +
                //"  and parent.nsfw in(0, viewer.nsfw) \n" +
                //"  and parent.spam in(0, viewer.spam ) \n" +
                "  and node.threadId = :threadId \n" +
                "  and viewer.posterId = :posterId \n" +
                "GROUP BY \n" +
                "  node.nsfw, node.spam, node.tos, \n" +
                "  node.postId, parent.threadId, node.treeId, content.title, \n" +
                "  moniker.monikerId, moniker.name, moniker.tagLine, moniker.postCount, moniker.showCounter, content.content, DATE_FORMAT(node.postDate, '%m/%d/%y %h:%i %p'), node.lft, node.rgt, moniker.posterId \n" +
                "ORDER BY node.treeId, node.lft \n",
                 PostView.class
            )
            .setParameter("threadId", threadId)
            .setParameter("posterId", (posterId >= 0) ? posterId : 0)
            .getResultList();
            
            tx.commit();
            em.close();
        } catch(Exception e) {
            System.err.println("Error getting posts: " + "T: " + threadId + " / P: " + posterId + " - " + e.getMessage());
            e.printStackTrace();
        }
        return rs;
    }
    
    public ThreadDataView getThreadData(int threadId) {
        ThreadDataView rs = null;
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            rs = (ThreadDataView)(em.createNativeQuery(
                "select \n" +
                "  p.threadId, case when s.threadId is null then 0 else 1 end as sticky, min(p.postDate) as minThreadDate, max(p.postDate) as maxThreadDate, \n" +
                "  t.tos, t.nsfw, t.spam \n" +
                "from \n" +
                "  post p \n" +
                "  left outer join ( \n" +
                "    select spam, tos, nsfw from post where postId = :threadId and threadId = postId and lft = 1 \n" +
                "  ) as t on 1=1 \n" +
                "  left outer join sticky s on (p.threadId = s.threadId and s.active =1) \n" +
                "where \n" +
                "  p.threadId = :threadId \n" +
                "group by p.threadId, s.threadId; \n",
                ThreadDataView.class
            )
            .setParameter("threadId", threadId)
            .getSingleResult());
            
            tx.commit();
            em.close();
        } catch(Exception e) {
            System.err.println("Error getting post dates: " + "T: " + threadId + " - " + e.getMessage());
            e.printStackTrace();
        }
        return rs;
    }
    
    public List<ThreadList> getTopThreads(int posterId, int topCount) {
        return getTopThreads(posterId, topCount, 0);
    }
    
    
    public List<ThreadList> getTopThreads(int posterId, int topCount, int page) {
        if(page < 0)
            page = 0;
        
        List<ThreadList> rs = null;
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            rs = em.createNativeQuery(
                "select \n" +
                "  s.posterid as sticky, \n" +
                "  p.threadId, \n" +
                "  (select sum(rgt)/2 from post where threadid = p.threadId and lft = 1) as postCount, \n" +
                "  CONCAT('STICKY: ', pc.title) as title, \n" +
                "  (select max(postDate) from post where threadid = p.threadId) as lastPost, \n" +
                "  0 as power, \n" +
                " p.tos, p.nsfw, p.spam \n" +
                "from \n" +
                "  post p, \n" +
                "  post c, \n" +
                "  postcontent pc, \n" +
                "  sticky s \n" +
                "where \n" +
                "  p.postId = pc.postId \n" +
                "  and p.lft = 1 \n" +
                "  and p.threadId = p.treeId \n" +
                "  and p.threadId = c.threadId \n" +
                "  and p.treeId = c.treeId \n" +
                "  and c.lft between p.lft and p.rgt \n" +
                //"  and p.tos = 0 \n" +
                "  and s.threadid = p.threadid \n" +
                "group by \n" +
                "  sticky, \n" +
                "  p.threadId, \n" +
                "  c.treeId, \n" +
                "  postCount, \n" +
                "  pc.title, \n" +
                "  lastPost, \n" +
                "  power \n" +
                "UNION \n" +
                "select 0 as sticky, p.threadId, postcnt.posts as postCount, pc.title, max(postdt.postdate) lastPost, 0 as power, \n" +
                " p.tos, p.nsfw, p.spam \n" +
                "from \n" +
                "  post p, \n" +
                "  post postdt, \n" +
                "  postcontent pc, \n" +
                "  ( \n" +
                "    select threadid, sum(rgt)/2 as posts \n" +
                "    from post \n" +
                "    where treeid = postid \n" +
                "    group by threadid \n" +
                "  ) as postcnt \n" +
                //"  left outer join poster viewer on 1=1 \n" +
                "where \n" +
                "  p.postId = pc.postId \n" +
                //"  and viewer.posterId = :posterId \n" +
                "  and p.postid not in (select threadId from sticky) \n" +
                //"  and p.nsfw in(0, viewer.nsfw) \n" +
                //"  and p.spam in(0, viewer.spam ) \n" +
                //"  and p.tos = 0 \n" +
                "  and p.lft = 1 \n" +
                "  and p.threadId = p.treeId \n" +
                "  and p.threadId = postcnt.threadId \n" +
                "  and p.threadId = postdt.threadId \n" +
                "group by p.threadId, pc.title, postcnt.posts \n" +
                "order by sticky desc, lastPost desc \n",                
                ThreadList.class
            )
            .setMaxResults(topCount)
            .setFirstResult(page*topCount)
            //.setParameter("posterId", (posterId >= 0) ? posterId : 0)
            .getResultList();
            
            tx.commit();
            em.close();
        } catch(Exception e) {
            System.err.println("Error getting top threads: " + "cnt: " + topCount + " / p: " + posterId + " - " + e.getMessage());
            e.printStackTrace();
        }
                
        return rs;
    }
    public List<ThreadList> searchThreads(int posterId, int topCount, String searchString) {
        List<ThreadList> rs = null;
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            rs = em.createNativeQuery(
                "select distinct xp.threadId, thrdCont.title, xp.nsfw, xp.spam, 0 as tos, max(xp.postDate) as lastPost, thrd.rgt/2 as postCount, round(sum(MATCH (xpc.title,xpc.content) AGAINST (:searchString)), 2) as power \n" +
                "from \n" +
                "  postcontent xpc, post xp, post thrd, postcontent thrdCont left outer join poster viewer on 1=1 \n" +
                "where \n" +
                "  xpc.postId = xp.postId \n" +
                "  and xp.threadId = thrd.threadId \n" +
                "  and thrd.lft = 1 \n" +
                "  and viewer.posterId = :posterId \n" +
                "  and xp.nsfw in(0, viewer.nsfw) \n" +
                "  and xp.spam in(0, viewer.spam ) \n" +
                "  and xp.tos = 0 \n" +
                "  and thrd.postId = thrdCont.postId \n" +
                "  and thrdCont.title is not null \n" +
                "  and MATCH (xpc.title,xpc.content) AGAINST (:searchString) \n" +
                "group by xp.threadId, thrdCont.title, xp.nsfw, xp.spam, thrd.rgt/2 \n" +
                "order by sum(MATCH (xpc.title,xpc.content) AGAINST (:searchString)) desc \n",
                 ThreadList.class
            )
            .setMaxResults(topCount)
            .setParameter("posterId", (posterId >= 0) ? posterId : 0)
            .setParameter("searchString", searchString)
            .setParameter("searchString", searchString)
            .setParameter("searchString", searchString)
            .getResultList();
            
            tx.commit();
            em.close();
        } catch(Exception e) {
            System.err.println("Error searching threads: " + "cnt: " + topCount + " / p: " + posterId + " / s: " + searchString + " - " + e.getMessage());
            e.printStackTrace();
        }
        return rs;
    }

    public List<ThreadList> searchThreadsAdv(int posterId, int topCount, String searchString, String startDate, String stopDate, boolean scoreSearchType, boolean scoreSearchOrder) {
        if(!scoreSearchType && scoreSearchOrder)
            return null;
        if(startDate == null || startDate.trim().equals("") || stopDate == null || stopDate.trim().equals(""))
            return null;
        
        
        List<ThreadList> rs = null;
        try {
            EntityManager em = util.getPersistenceFactory().createEntityManager();
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            
            if(scoreSearchType) {
                String orderBy = scoreSearchOrder?"order by sum(MATCH (xpc.title,xpc.content) AGAINST (:searchString)) desc \n":"order by date_format(max(xp.postDate), '%m/%d/%y %H:%i') desc \n";
                rs = em.createNativeQuery(
                    "select distinct xp.threadId, xp.nsfw, xp.spam, 0 as tos, thrdCont.title, max(xp.postDate) as lastPost, thrd.rgt/2 as postCount, round(sum(MATCH (xpc.title,xpc.content) AGAINST (:searchString)), 2) as power \n" +
                    "from \n" +
                    "  postcontent xpc, post xp, post thrd, postcontent thrdCont left outer join poster viewer on 1=1 \n" +
                    "where \n" +
                    "  xpc.postId = xp.postId \n" +
                    "  and xp.threadId = thrd.threadId \n" +
                    "  and thrd.lft = 1 \n" +
                    "  and viewer.posterId = :posterId \n" +
                    "  and xp.nsfw in(0, viewer.nsfw) \n" +
                    "  and xp.spam in(0, viewer.spam ) \n" +
                    "  and xp.tos = 0 \n" +
                    "  and thrd.postId = thrdCont.postId \n" +
                    "  and thrdCont.title is not null \n" +
                    "  and MATCH (xpc.title,xpc.content) AGAINST (:searchString) \n" +
                    "  and xp.postDate between :startDate and :stopDate \n" +
                    "group by xp.threadId, thrdCont.title, xp.nsfw, xp.spam, thrd.rgt/2 \n" +
                    orderBy,
                     ThreadList.class
                )
                .setMaxResults(topCount)
                .setParameter("posterId", (posterId >= 0) ? posterId : 0)
                .setParameter("searchString", searchString)
                .setParameter("searchString", searchString)
                .setParameter("searchString", searchString)
                .setParameter("startDate", startDate)
                .setParameter("stopDate", stopDate)
                .getResultList();
            } else {
                rs = em.createNativeQuery(
                    "select xp.threadId, xpc.title, max(xp.postDate) as lastPost, xp.rgt/2 as postCount, 0 as power \n" +
                    "from \n" +
                    "  postcontent xpc, post xp left outer join poster viewer on 1=1 \n" +
                    "where \n" +
                    "  xp.postId = xp.threadId \n" +
                    "  and xp.postId = xp.treeId \n" +
                    "  and xp.lft = 1 \n" +
                    "  and xp.postId = xpc.postId \n" +
                    "  and viewer.posterId = :posterId \n" +
                    "  and xp.nsfw in(0, viewer.nsfw) \n" +
                    "  and xp.spam in(0, viewer.spam ) \n" +
                    "  and xp.tos = 0 \n" +
                    "  and xpc.title is not null \n" +
                    "  and xpc.title like '%" + searchString + "%' \n" +
                    "  and xp.postDate between :startDate and :stopDate \n" +
                    "group by xp.threadId, xpc.title, xp.rgt/2 \n" +
                    "order by xp.postDate desc \n",
                     ThreadList.class
                )
                .setMaxResults(topCount)
                .setParameter("posterId", (posterId >= 0) ? posterId : 0)
                .setParameter("startDate", startDate)
                .setParameter("stopDate", stopDate)
                .getResultList();                
            }
            
            tx.commit();
            em.close();
        } catch(Exception e) {
            System.err.println("Error searching threads: " + "cnt: " + topCount + " / p: " + posterId + " / s: " + searchString + " - " + e.getMessage());
            e.printStackTrace();
        }
        return rs;
    }    
    
    
}


